clear all
clc

load('D:\My Documents\CIRANO\db_mySQL\tp_2012Mar5_MR7_WR.mat','cohTpGrpStd','conditions','numEdgesStd','year_list');
numEdges = numEdgesStd;

conn = database('BDC','','');
%yr = 11; %2008
%yr = 3; %2000
% yr = 4; %2001
%yr = 5; %2002
%yr = 6; %2003
%yr = 7; %2004
%yr = 8;%2005
%yr = 9; %2006
%yr = 10;%2007
yr = [4, 5, 9, 11, 12];
T = 1; % time horizon
R = 0.45; % recovery rate
f = 0.21; % factor loading
numSimulate = 10000;
SG = cellstr(char('Sample 1: [$50k,+)','Sample 2A: [$50k, 250k)','Sample 2B: [$250k, 1000k)','Sample 2C: [$1000k, +)'));


tic

for yrIndex = 5:5 %1: length(yr)
    lossGrp = zeros(numSimulate, length(conditions)); 
for grp = 1:length(conditions)
    
% calcuate exposure of assigned year
condition = char(conditions(grp));
% sqlquery1 = strcat(['select grpRate(modiRate) as grpRate, avg(PPDOS_sum), count(customer) from helena20111207.final_v4_tbl as t where Year(modiFisDate) =  ''' num2str(year_list(yr)) ''' and Month(modiFisDate) = 12 and ' condition ' group by grpRate;']);
sqlquery2 = strcat(['select customer, grpRate(modiRate) as grpRate, PPDOS_sum from helena20111207.final_v4_tbl where Year(modiFisDate) =  ''' num2str(year_list(yr(yrIndex))) ''' and Month(modiFisDate) = 12 and ' condition ' and modiRate <> -1 group by customer;']);
exposure = fetch(conn, sqlquery2);

% set portfolio distribution of rating and exposure
ExposureCreditRating = cell2mat(exposure(:,2));
ExposureCreditRating = ExposureCreditRating.*2 - 2; % convert rating into integer
NumberOfExposures = length(exposure);
Loss=cell2mat(exposure(:,3)).*(1-R);
display(['Portfolio total firm# in group ' num2str(grp) ' is ' NumberOfExposures])


%Generate Thresholds for Transitions
TransitionMatrix(:,:) = cohTpGrpStd(grp, yr(yrIndex), 1:end-1,:)./100;
RatingThresholds=norminv(TransitionMatrix(:,end));
ThresholdTable=RatingThresholds(ExposureCreditRating);

% simulation
display(['start simulation for group ' num2str(grp)]);

for j=1:numSimulate
    ZTilda = randn;
    X=f*ZTilda+sqrt(1-f^2)*randn(NumberOfExposures,1);    
    lossGrp(j, grp)=sum(Loss(X<ThresholdTable));   
end
    
end
filename = strcat('D:\My Documents\CIRANO\db_mySQL\simulation_yr', num2str(yr(yrIndex)), '_10k_cohort_CreditMetric.mat');
save (filename)
clear sqlquery2 exposure ExposureCreditRating Loss ThresholdTable lossGrp
end

toc

% figure
% for grp = 1:4
% subplot(2,2,grp);
% a = lossGrp(:,grp);
% hist(a,50);
% title(SG(grp));
% xlabel('Loss');
% ylabel('Frequency')    
% grid on
% end
% [y,xi] = ksdensity(loss);
% plot(xi,y); 
